/*
	This Procedure generats all the Running and Hold lots in Chiptrac System based on the Search criteria.
	If Report Type is "ALL" then we will load Transit lots in addition to Running and Hold lots.

	Usage	:	EXEC sp_wip_info_report '0','1143,1151','Chartered','0','12/05/2006','Running','ALL','0'
	 		EXEC sp_wip_info_data_dump_report 'FG','0','0','0','03/15/2009','0','all','','0'

	Views used in this Procedure are as follows.
	DROP VIEW vw_Fab_Wip_View
	DROP VIEW vw_Fab_Option_Wip_View
	DROP VIEW vw_WaferStore_Wip_View
	DROP VIEW vw_DieBank_Wip_View
	DROP VIEW vw_Assembly_Wip_View
	DROP VIEW vw_Test_FPO_Wip_View
	DROP VIEW vw_Test_FT_Wip_View
	DROP VIEW vw_ClassStore_Wip_View
	DROP VIEW vw_FG_Wip_View
	
	SELECT * FROM vw_Fab_Wip_View
	SELECT * FROM vw_Fab_Option_Wip_View
	SELECT * FROM vw_WaferStore_Wip_View
	SELECT * FROM vw_DieBank_Wip_View
	SELECT * FROM vw_Assembly_Wip_View
	SELECT * FROM vw_Test_FPO_Wip_View
	SELECT * FROM vw_Test_FT_Wip_View
	SELECT * FROM vw_ClassStore_Wip_View
	SELECT * FROM vw_FG_Wip_View

*/
ALTER  PROC sp_wip_info_data_dump_report

@PROCESS_TYPE		VARCHAR(50),
@PROCESSSTEP_IDS	VARCHAR(200),
@VENDOR			VARCHAR(50),
@LOT_CATEGORY		VARCHAR(20),
@ASOFDATE		VARCHAR(50),
@STATUS			VARCHAR(20),
@REPORT_TYPE		VARCHAR(20),
@SEARCH_PART_NO		varchar(50),
@DEFAULT_PROMPT		VARCHAR(50)

AS
BEGIN

--Final Result table to store all the running lots Chiptrac based on the Search criteria
CREATE TABLE #WIP_RESULTS ( PROCESSSTEP_ID INT, LOCATION VARCHAR(50), LOT_NUMBER VARCHAR(50), FAB_LOT_NUMBER VARCHAR(50),
			LOT_CATEGORY VARCHAR(50), WAFER_QTY_IN INT, QTY_IN INT,	DATE_IN DATETIME, STAGE_NAME VARCHAR(50), 
			STAGE_POSITION VARCHAR(50), LOT_CURRENT_STATUS VARCHAR(50), EXPECTED_DATE_OUT DATETIME, 
			OUTLOOK_DATE DATETIME, GROSS_DIE INT,WAFER_ID VARCHAR(200), 
			PART_NUMBER VARCHAR(50),BASE_PRODUCT VARCHAR(50), BASE_PRODUCT_ID INT,
			PS_TYPE_NAME VARCHAR(50),PS_TYPE_ID INT,THINKNESS VARCHAR(50),GRIND VARCHAR(50),
			SL_FLOW VARCHAR(50), SL_TEST_PROGRAM VARCHAR(50),SL_HOLD_DATE DATETIME,SL_TESTER VARCHAR(50),
			MASK VARCHAR(50),LABLE_NAME VARCHAR(50),PACKAGE_TYPE VARCHAR(50), ASSY_REL_NO VARCHAR(50), ASSY_REL_DATE VARCHAR(50), Date_Code VARCHAR(50)
		 )
--Fg Results table.
CREATE TABLE #FG_RESULTS ([PROCESSSTEP ID] INT,[FG VENDOR] VARCHAR(50), [ASSEMBLY LOT NUMBER] VARCHAR(50),[FAB LOT NUMBER] VARCHAR(50),
		     	[LOT CATEGORY] VARCHAR(50), [QTY IN] INT,[DATE IN] DATETIME,[LOT STATUS] VARCHAR(50),
			[DATE CODE] VARCHAR(50),[EXPIRY DATE] INT,[AGE] INT,[EXPECTED DATE OUT] DATETIME,
			[PART NUMBER] VARCHAR(50),[PIN COUNT] VARCHAR(50),[PART STATUS] VARCHAR(50),[PACKAGE TYPE] VARCHAR(50),
			[ASSEMBLY VENDOR] VARCHAR(50),[ASSEMBLY PROCESSSTEP ID] INT,[TEST PROCESSSTEP ID] INT, [TEST PROGRAM] VARCHAR(100), [TEST OUT DATE] DATETIME )
			
--Intermediate tables
CREATE TABLE #STEP_IDS_TABLE  ( STEP_ID INT )
CREATE TABLE #ASSEMBLY_PROCESSSTEP_TABLE  ( FGPROCESSSTEP_ID INT, ASSEMBLY_PROCESSSTEP_ID INT, TEST_PROCESSSTEP_ID INT )

/* 
	In this table when no Process Type is selected we will load BaseProduct Ids and 
	if Process Type is selected we we will loaad corresponding process Processstep_ids
*/
IF @PROCESSSTEP_IDS <> '0'
BEGIN
	IF @REPORT_TYPE = 'ALL' OR @REPORT_TYPE = 'WIP' OR @REPORT_TYPE = 'INVENTORY'
	BEGIN
		IF ( @PROCESS_TYPE = '0' OR @PROCESS_TYPE = 'WIP' OR @PROCESS_TYPE = 'INVENTORY' )
		BEGIN
			INSERT INTO #STEP_IDS_TABLE SELECT PROCESSSTEP_ID FROM PRODUCT WHERE P_PARENT_PRODUCT_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @PROCESSSTEP_IDS ) ) 
		END
		ELSE
		BEGIN
			INSERT INTO #STEP_IDS_TABLE SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ( @PROCESSSTEP_IDS ) 
		END
	END
END

IF @PROCESS_TYPE = 'TAPE'
BEGIN
	SET @PROCESS_TYPE = 'TAPE & REEL'
END

--select * from #STEP_IDS_TABLE

IF @ASOFDATE = '0'
BEGIN
	DECLARE @FAB_SQLSTRING VARCHAR(2000)
	DECLARE @FAB_OPTION_SQLSTRING VARCHAR(2000) 
	DECLARE @WS_SQLSTRING VARCHAR(2000) 
	DECLARE @SORT_SQLSTRING VARCHAR(2000) 
	DECLARE @DB_SQLSTRING VARCHAR(2000) 
	DECLARE @ASSEMBLY_SQLSTRING VARCHAR(2000) 
	DECLARE @TEST_FPO_SQLSTRING VARCHAR(2000)
	DECLARE @TEST_FT_SQLSTRING VARCHAR(2000) 
	DECLARE @CS_SQLSTRING VARCHAR(2000)
	DECLARE @CLASS_STORE_SQLSTRING VARCHAR(2000)
	DECLARE @EOL_SQLSTRING VARCHAR(2000)
	DECLARE @FG_SQLSTRING VARCHAR(2000)
	DECLARE @TRANSIT_STRING VARCHAR(4000)
	DECLARE @FGTRANSIT_STRING VARCHAR(4000)

	--HERE WE ARE RETREVNG FG DATA SEPARATELY THIS IS CALLED WHEN FG IS SELECTED SEPARATELY.
	DECLARE @FG_REPORT	VARCHAR(2000) 
	
	IF ( @PROCESS_TYPE = '0' OR @PROCESS_TYPE = 'FT' OR @PROCESS_TYPE = 'Pre Test' OR @PROCESS_TYPE = 'EOL' OR @PROCESS_TYPE = 'TAPE & REEL' OR @PROCESS_TYPE = 'FG' )
	BEGIN
		DECLARE @FG_PROCESSSTEP_ID 	INT
		DECLARE @ASSM_PROCESSSTEP_ID 	INT
		DECLARE @TEST_PROCESSSTEP_ID INT
		DECLARE @PROCESSSTEP_STRING 	VARCHAR(2000)
		
		SET @PROCESSSTEP_STRING = N'
			DECLARE PROCESSSTEP_CURSOR CURSOR FOR 
			SELECT 
				DISTINCT LT.PROCESSSTEP_ID 
			FROM 
				LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS 
			WHERE 
				LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID 
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID '
			
			IF @PROCESS_TYPE = '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND (PS_TYPE = ''FT'' OR PS_TYPE = ''Pre Test'' OR PS_TYPE = ''EOL'' OR PS_TYPE = ''TAPE & REEL'' OR PS_TYPE = ''FG'') '
			END
			ELSE IF @PROCESS_TYPE = 'FT'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE = ''FT'' '
			END
			ELSE IF @PROCESS_TYPE = 'Pre Test'
			BEGIN 
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE = ''Pre Test'' '
			END
			ELSE IF @PROCESS_TYPE = 'EOL'
			BEGIN 
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE = ''EOL'' '
			END
			ELSE IF @PROCESS_TYPE = 'FG'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE = ''FG'' '
			END
			ELSE IF @PROCESS_TYPE = 'inventory'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( PS_TYPE = ''FG'' OR PS_TYPE = ''EOL'' OR PS_TYPE = ''Pre Test'' OR PS_TYPE = ''TAPE & REEL'' ) '
			END
			ELSE IF @PROCESS_TYPE = 'wip'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( PS_TYPE = ''FT'' ) '
			END
			ELSE IF @PROCESS_TYPE = 'TAPE & REEL'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE = ''TAPE & REEL'' '
			END

			IF @PROCESSSTEP_IDS <> '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
			END

			IF @VENDOR <> '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.LOCATION_ID  IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = '''+ @VENDOR +''''+')'
			END

			IF @LOT_CATEGORY <> '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
			END

			IF @STATUS = '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
			END
			ELSE
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
			END

			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
			END

			SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' UNION

			SELECT 
				DISTINCT LT.PROCESSSTEP_ID 
			FROM 
				SHIPPED_LOT SL,LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS 
			WHERE 
				SL.LOT_ID = LT.LOT_ID
				AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID 
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND STATUS !=''CLOSE'' '
			
			IF @PROCESS_TYPE = '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND (PS_TYPE = ''FT'' OR PS_TYPE = ''Pre Test'' OR PS_TYPE = ''EOL'' OR PS_TYPE = ''TAPE & REEL'' OR PS_TYPE = ''FG'') '
			END
			ELSE IF @PROCESS_TYPE = 'FT'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE = ''FT'' '
			END
			ELSE IF @PROCESS_TYPE = 'Class Store'
			BEGIN 
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE = ''Pre Test'' '
			END
			ELSE IF @PROCESS_TYPE = 'EOL'
			BEGIN 
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE = ''EOL'' '
			END
			ELSE IF @PROCESS_TYPE = 'FG'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE = ''FG'' '
			END
			ELSE IF @PROCESS_TYPE = 'inventory'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( PS_TYPE = ''FG'' OR PS_TYPE = ''EOL'' OR PS_TYPE = ''Pre Test'' OR PS_TYPE = ''TAPE & REEL'' ) '
			END
			ELSE IF @PROCESS_TYPE = 'wip'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND ( PS_TYPE = ''FT'' or PS_TYPE = ''FPO'' ) '
			END
			ELSE IF @PROCESS_TYPE = 'TAPE & REEL'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND PS_TYPE = ''TAPE & REEL'' '
			END

			IF @PROCESSSTEP_IDS <> '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
			END

			IF @VENDOR <> '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.LOCATION_ID  IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = '''+ @VENDOR +''''+')'
			END

			IF @LOT_CATEGORY <> '0'
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND LT.LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
			END

			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
			BEGIN
				SET @PROCESSSTEP_STRING = @PROCESSSTEP_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
			END

			--PRINT '@PROCESSSTEP_STRING'+@PROCESSSTEP_STRING
			EXEC (@PROCESSSTEP_STRING)

			OPEN PROCESSSTEP_CURSOR 
			FETCH NEXT FROM PROCESSSTEP_CURSOR
				INTO @FG_PROCESSSTEP_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				SELECT @ASSM_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @FG_PROCESSSTEP_ID ) WHERE PS_TYPE = 'ASSEMBLY'
				SELECT @TEST_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @FG_PROCESSSTEP_ID ) WHERE PS_TYPE = 'FT'
				
				INSERT INTO #ASSEMBLY_PROCESSSTEP_TABLE (FGPROCESSSTEP_ID,ASSEMBLY_PROCESSSTEP_ID, TEST_PROCESSSTEP_ID) VALUES (@FG_PROCESSSTEP_ID,@ASSM_PROCESSSTEP_ID, @TEST_PROCESSSTEP_ID)
			
					SET @FG_PROCESSSTEP_ID = NULL
					SET @ASSM_PROCESSSTEP_ID = NULL
					SET @TEST_PROCESSSTEP_ID = NULL
			
				FETCH NEXT FROM PROCESSSTEP_CURSOR INTO @FG_PROCESSSTEP_ID
			END
				 																
			CLOSE PROCESSSTEP_CURSOR
			DEALLOCATE PROCESSSTEP_CURSOR 
		
		--SELECT * FROM #ASSEMBLY_PROCESSSTEP_TABLE
	END
	
	-- Getting the Gross Die for Die Bank Processstep Ids.
	-- here we are considering all die bank parts as there will be not die bank lots in lot table.
	IF @PROCESS_TYPE = '0' OR @PROCESS_TYPE = 'DIE BANK' OR @PROCESS_TYPE = 'INVENTORY'
	BEGIN	
		DECLARE @DB_PROCESSSTEP_ID 	INT
		DECLARE @FAB_PROCESSSTEP_ID 	INT
		DECLARE @FABPROCESSSTRING 	VARCHAR(1000)
		
		CREATE TABLE #FAB_PROCESSSTEP_TABLE  ( DBPROCESSSTEP_ID INT, FAB_PROCESSSTEP_ID INT )		
		
		SET @FABPROCESSSTRING = N'
			DECLARE PROCESSSTEP_CURSOR CURSOR FOR 
			SELECT 
				DISTINCT LT.PROCESSSTEP_ID 
			FROM 
				LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS 
			WHERE 
				LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID 
				AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
				AND PS_TYPE = ''die bank'' '

			IF @PROCESSSTEP_IDS <> '0'
			BEGIN
				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND p.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
			END

			IF @VENDOR <> '0'
			BEGIN
				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND LT.LOCATION_ID  IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = '''+ @VENDOR +''''+')'
			END

			IF @LOT_CATEGORY <> '0'
			BEGIN
				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND LT.LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
			END

			IF @STATUS = '0'
			BEGIN
				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
			END
			ELSE
			BEGIN
				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
			END

			IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
			BEGIN
				SET @FABPROCESSSTRING = @FABPROCESSSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
			END

			EXEC (@FABPROCESSSTRING)

			OPEN PROCESSSTEP_CURSOR 
			FETCH NEXT FROM PROCESSSTEP_CURSOR
				INTO @DB_PROCESSSTEP_ID
			WHILE @@FETCH_STATUS = 0
			BEGIN
				--PRINT '@DB_PROCESSSTEP_ID'+CONVERT(VARCHAR,@DB_PROCESSSTEP_ID)
				SELECT @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM FN_GET_PROCESS_FLOW_BY_PROCESSSTEP_ID ( @DB_PROCESSSTEP_ID ) WHERE PS_TYPE = 'FAB'
				
				INSERT INTO #FAB_PROCESSSTEP_TABLE (DBPROCESSSTEP_ID,FAB_PROCESSSTEP_ID) VALUES (@DB_PROCESSSTEP_ID,@FAB_PROCESSSTEP_ID)
			
					SET @DB_PROCESSSTEP_ID = NULL
					SET @FAB_PROCESSSTEP_ID = NULL
			
				FETCH NEXT FROM PROCESSSTEP_CURSOR INTO @DB_PROCESSSTEP_ID
			END
				 																
			CLOSE PROCESSSTEP_CURSOR
			DEALLOCATE PROCESSSTEP_CURSOR 
		
		--SELECT * FROM #FAB_PROCESSSTEP_TABLE
	END
	
	--Inserting Fab Data
	--SELECT * FROM vw_Fab_Wip_View
	SET @FAB_SQLSTRING = N'
	INSERT INTO #WIP_RESULTS 
	      ( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, STAGE_NAME, 
		STAGE_POSITION, LOT_CURRENT_STATUS, EXPECTED_DATE_OUT,GROSS_DIE, 
		OUTLOOK_DATE, WAFER_ID, PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, 
		PS_TYPE_NAME,PS_TYPE_ID ) 
	SELECT  
		PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, STAGE_NAME, 
		STAGE_POSITION, LOT_CURRENT_STATUS, FL_EXPECTED_DATE_OUT,GROSS_DIE_WAFER, 
		FL_OUTLOOK_DATE, WAFER_ID, PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, 
		PS_TYPE,PS_TYPE_ID
	FROM 
		vw_Fab_Wip_View  WHERE 1=1 '
	
	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @FAB_SQLSTRING = @FAB_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @FAB_SQLSTRING = @FAB_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @FAB_SQLSTRING = @FAB_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
	END
	IF @STATUS <> '0'
	BEGIN
		SET @FAB_SQLSTRING = @FAB_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
	END	
	IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
	BEGIN
		SET @FAB_SQLSTRING = @FAB_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END
	
	--Insertig Wafer Store Data	
	SET @WS_SQLSTRING = N'
	INSERT INTO #WIP_RESULTS 
	      ( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
		GROSS_DIE, WAFER_ID, PART_NUMBER, BASE_PRODUCT, 
		BASE_PRODUCT_ID,PS_TYPE_NAME,PS_TYPE_ID ) 
	SELECT  
		PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
		GROSS_DIE_WAFER,WAFER_ID,PART_NUMBER, BASE_PRODUCT, 
		BASE_PRODUCT_ID, PS_TYPE_NAME,PS_TYPE_ID 
	FROM 
		vw_WaferStore_Wip_View WHERE 1=1 '

	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @WS_SQLSTRING = @WS_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @WS_SQLSTRING = @WS_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @WS_SQLSTRING = @WS_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
	END
	IF @STATUS <> '0'
	BEGIN
		SET @WS_SQLSTRING = @WS_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
	END	
	IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
	BEGIN
		SET @WS_SQLSTRING = @WS_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END

	--Insertig Sort Data	
	SET @SORT_SQLSTRING = N'
	INSERT INTO #WIP_RESULTS 
	      ( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
		GROSS_DIE, WAFER_ID, PART_NUMBER, BASE_PRODUCT, 
		BASE_PRODUCT_ID,PS_TYPE_NAME,PS_TYPE_ID ) 
	SELECT  
		PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, WAFER_QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
		GROSS_DIE_WAFER,WAFER_ID,PART_NUMBER, BASE_PRODUCT, 
		BASE_PRODUCT_ID, PS_TYPE_NAME,PS_TYPE_ID 
	FROM 
		vw_Sort_Wip_View WHERE 1=1 '

	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @SORT_SQLSTRING = @SORT_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @SORT_SQLSTRING = @SORT_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @SORT_SQLSTRING = @SORT_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
	END
	IF @STATUS <> '0'
	BEGIN
		SET @SORT_SQLSTRING = @SORT_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
	END	
	IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
	BEGIN
		SET @SORT_SQLSTRING = @SORT_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END

	-- Inserting DIE BANK Data
	SET @DB_SQLSTRING = N'
	INSERT INTO #WIP_RESULTS 
		( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
		WAFER_ID,PART_NUMBER, BASE_PRODUCT, 
		BASE_PRODUCT_ID,PS_TYPE_NAME,PS_TYPE_ID,GROSS_DIE ) 

	SELECT  
		VDB.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
		WAFER_ID, PART_NUMBER, BASE_PRODUCT, 
		BASE_PRODUCT_ID, PS_TYPE_NAME,PS_TYPE_ID,
		(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER PP,#FAB_PROCESSSTEP_TABLE FPT  WHERE PARAMETER_KEY = ''Gross Die/Wafer'' AND PP.PROCESSSTEP_ID = FPT.FAB_PROCESSSTEP_ID AND DBPROCESSSTEP_ID = VDB.PROCESSSTEP_ID ) AS GROSS_DIE
	FROM 
		vw_DieBank_Wip_View VDB WHERE 1=1 '
	
	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @DB_SQLSTRING = @DB_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @DB_SQLSTRING = @DB_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @DB_SQLSTRING = @DB_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
	END
	IF @STATUS <> '0'
	BEGIN
		SET @DB_SQLSTRING = @DB_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
	END
	IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
	BEGIN
		SET @DB_SQLSTRING = @DB_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END

	--Inserting Assembly Data
	SET @ASSEMBLY_SQLSTRING = N'
	INSERT INTO #WIP_RESULTS 
		( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, STAGE_NAME, 
		STAGE_POSITION, LOT_CURRENT_STATUS, EXPECTED_DATE_OUT,
		THINKNESS, GRIND, PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, 
		PS_TYPE_NAME,PS_TYPE_ID,PACKAGE_TYPE, Date_Code ) 
	SELECT  
		VA.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, ISNULL( FAB_LOT_NUMBER, LOT_NUMBER),
		LOT_CATEGORY, WAFER_QTY_IN, QTY_IN, DATE_IN, STAGE_NAME, 
		STAGE_POSITION, LOT_CURRENT_STATUS, EXPECTED_DATE_OUT, 
		THICKNESS, GRIND,PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID,
		PS_TYPE,PS_TYPE_ID,
		(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PROCESSSTEP_ID = VA.PROCESSSTEP_ID ) AS PACKAGE_TYPE,
		LOT_DATE_CODE
	FROM 
		vw_Assembly_Wip_View VA WHERE PS_TYPE = ''ASSEMBLY'' '
	
	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @ASSEMBLY_SQLSTRING = @ASSEMBLY_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @ASSEMBLY_SQLSTRING = @ASSEMBLY_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @ASSEMBLY_SQLSTRING = @ASSEMBLY_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
	END
	IF @STATUS <> '0'
	BEGIN
		SET @ASSEMBLY_SQLSTRING = @ASSEMBLY_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
	END
	IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
	BEGIN
		SET @ASSEMBLY_SQLSTRING = @ASSEMBLY_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END

	--Inserting Class Store Data
	SET @CLASS_STORE_SQLSTRING = N'
	INSERT INTO #WIP_RESULTS 
		( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS,
		PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, 
		PS_TYPE_NAME,PS_TYPE_ID,PACKAGE_TYPE, Date_Code ) 
	SELECT  
		VFPO.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, ISNULL( FAB_LOT_NUMBER, LOT_NUMBER),
		LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS,  
		PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID,
		PS_TYPE,PS_TYPE_ID,
		(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER PP,#ASSEMBLY_PROCESSSTEP_TABLE APT  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND FGPROCESSSTEP_ID = VFPO.PROCESSSTEP_ID ) AS PACKAGE_TYPE,
		Lot_Date_Code
	FROM 
		vw_ClassStore_Wip_View VFPO WHERE 1=1  '
	
	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @CLASS_STORE_SQLSTRING = @CLASS_STORE_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @CLASS_STORE_SQLSTRING = @CLASS_STORE_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @CLASS_STORE_SQLSTRING = @CLASS_STORE_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
	END
	IF @STATUS <> '0'
	BEGIN
		SET @CLASS_STORE_SQLSTRING = @CLASS_STORE_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
	END
	IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
	BEGIN
		SET @CLASS_STORE_SQLSTRING = @CLASS_STORE_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END

	--Inserting Test FT Data
	SET @TEST_FT_SQLSTRING = N'
	INSERT INTO #WIP_RESULTS 
		( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, QTY_IN, DATE_IN, STAGE_NAME, 
		STAGE_POSITION, LOT_CURRENT_STATUS, EXPECTED_DATE_OUT,
		PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, 
		PS_TYPE_NAME,PS_TYPE_ID,PACKAGE_TYPE, Date_Code ) 
	SELECT  
		VFT.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, ISNULL( FAB_LOT_NUMBER, LOT_NUMBER),
		LOT_CATEGORY, QTY_IN, DATE_IN, STAGE_NAME, 
		STAGE_POSITION, LOT_CURRENT_STATUS, FTL_EXPECTED_DATE, 
		PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID,
		PS_TYPE,PS_TYPE_ID,
		(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER PP,#ASSEMBLY_PROCESSSTEP_TABLE APT  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND FGPROCESSSTEP_ID = VFT.PROCESSSTEP_ID ) AS PACKAGE_TYPE,
		Lot_Date_Code
	FROM 
		vw_Test_FT_Wip_View VFT WHERE PS_TYPE = ''FT'' '
	
	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @TEST_FT_SQLSTRING = @TEST_FT_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @TEST_FT_SQLSTRING = @TEST_FT_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @TEST_FT_SQLSTRING = @TEST_FT_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
	END
	IF @STATUS <> '0'
	BEGIN
		SET @TEST_FT_SQLSTRING = @TEST_FT_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
	END
	IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
	BEGIN
		SET @TEST_FT_SQLSTRING = @TEST_FT_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END

	--Inserting EOL Data
	SET @EOL_SQLSTRING = N'
	INSERT INTO #WIP_RESULTS 
		( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
		PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID, 
		PS_TYPE_NAME,PS_TYPE_ID,PACKAGE_TYPE, Date_Code ) 
	SELECT  
		VFPO.PROCESSSTEP_ID, L_VENDOR_NAME, LOT_NUMBER, ISNULL( FAB_LOT_NUMBER, LOT_NUMBER),
		LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS, 
		PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID,
		PS_TYPE,PS_TYPE_ID,
		(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER PP,#ASSEMBLY_PROCESSSTEP_TABLE APT  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND FGPROCESSSTEP_ID = VFPO.PROCESSSTEP_ID ) AS PACKAGE_TYPE,
		Lot_Date_Code
	FROM 
		vw_Eol_Wip_View VFPO WHERE 1=1 '
	
	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @EOL_SQLSTRING = @EOL_SQLSTRING + ' AND PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @EOL_SQLSTRING = @EOL_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @EOL_SQLSTRING = @EOL_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
	END
	IF @STATUS <> '0'
	BEGIN
		SET @EOL_SQLSTRING = @EOL_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
	END
	IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
	BEGIN
		SET @EOL_SQLSTRING = @EOL_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END

	--Inserting FG Data
	SET @FG_SQLSTRING = N'
	INSERT INTO #WIP_RESULTS 
		( PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS,EXPECTED_DATE_OUT,
		LABLE_NAME,PART_NUMBER,BASE_PRODUCT,BASE_PRODUCT_ID,PS_TYPE_NAME,PS_TYPE_ID,PACKAGE_TYPE, ASSY_REL_NO , ASSY_REL_DATE, Date_Code ) 

	SELECT  
		VFG.PROCESSSTEP_ID, L_VENDOR_NAME, VFG.LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY, QTY_IN, DATE_IN, LOT_CURRENT_STATUS,EXPECTED_DATE_OUT,
		LABLE_NAME,PART_NUMBER, BASE_PRODUCT, BASE_PRODUCT_ID,
		PS_TYPE_NAME,PS_TYPE_ID,
		(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER PP,#ASSEMBLY_PROCESSSTEP_TABLE APT  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND FGPROCESSSTEP_ID = VFG.PROCESSSTEP_ID ) AS PACKAGE_TYPE,
		ASSEMBLY_RELEASE_NO, ASSEMBLY_RELEASE_DATE, Lot_Date_Code
	FROM 
		vw_FG_Wip_View VFG LEFT OUTER JOIN ASSM_RELEASE_TABLE ON VFG.VENDOR_LOT_NUMBER = Assembly_Lot_Number  WHERE 1=1 '
	
	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @FG_SQLSTRING = @FG_SQLSTRING + ' AND VFG.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @FG_SQLSTRING = @FG_SQLSTRING + ' AND L_VENDOR_NAME = '''+ @VENDOR+''''
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @FG_SQLSTRING = @FG_SQLSTRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
	END
	IF @STATUS <> '0'
	BEGIN
		SET @FG_SQLSTRING = @FG_SQLSTRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
	END
	IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
	BEGIN
		SET @FG_SQLSTRING = @FG_SQLSTRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END
 
	--Inserting FG Data FOR FG REPORT.
	-- FOR GETTING ASSEMBLY VENDOR FOR THE FG LOT, FIRST CURSOR GETS THE ASSEMBLY PROCESSSTEP_IDS FOR DISTINCT FG PROCESSSTEP_IDS
	IF @PROCESS_TYPE = 'FG'
	BEGIN		
		SET @FG_REPORT = N'INSERT INTO #FG_RESULTS
		SELECT 
			LT.PROCESSSTEP_ID,L_VENDOR_NAME AS FG_VENDOR,
			LOT_NUMBER,FAB_LOT_NUMBER,LOT_CATEGORY,QTY_IN,DATE_IN,
			(CASE
				WHEN LOT_CURRENT_STATUS = ''RUNNING'' THEN ''ACTIVE''
				ELSE ''HOLD''
			END) AS LOT_CURRENT_STATUS,
			LOT_DATE_CODE,DATEDIFF( DD,DATE_IN,ISNULL( IL.EXPECTED_DATE_OUT,CURRENT_TIMESTAMP ) )AS EXPIRY_DATE,DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP ) AS lot_AGE,
			IL.EXPECTED_DATE_OUT as EXPECTED_DATE_OUT ,PART_NUMBER,
			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PIN'' AND PROCESSSTEP_ID = PS.PROCESSSTEP_ID ) AS PIN_COUNT,
			 P.PART_STATUS,
			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID ) AS PACKAGE_TYPE,
			--(SELECT L_VENDOR_NAME FROM LOT_TABLE LT1,LOCATION L1 WHERE LT1.LOCATION_ID = L1.LOCATION_ID AND LT1.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND LT1.FAB_LOT_NUMBER = LT.FAB_LOT_NUMBER AND LT1.LOT_NUMBER = LT.LOT_NUMBER ) --AND FGPROCESSSTEP_ID = LT.PROCESSSTEP_ID) 
			NULL AS ASSEMBLY_VENDOR,APT.ASSEMBLY_PROCESSSTEP_ID, APT.TEST_PROCESSSTEP_ID, '''', ''''
		FROM 
			INVENTORY_LOT IL,LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS,#ASSEMBLY_PROCESSSTEP_TABLE APT,LOCATION L
		WHERE 
			IL.LOT_ID = LT.LOT_ID
			AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
			AND PS.PROCESSSTEP_ID = APT.FGPROCESSSTEP_ID
			AND LT.LOCATION_ID = L.LOCATION_ID
			AND PS_TYPE = ''FG'' '
	
		IF @PROCESSSTEP_IDS <> '0'
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
		END
		IF @VENDOR <> '0'
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND L.L_VENDOR_NAME = '''+ @VENDOR +''''
		END
		IF @LOT_CATEGORY <> '0'
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
		END
		IF @STATUS = '0'
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
		END
		ELSE
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
		END
		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
		BEGIN
			SET @FG_REPORT = @FG_REPORT + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
		END
		--PRINT ('@FG_REPORT ----'+@FG_REPORT)

		SET @FGTRANSIT_STRING = N'INSERT INTO #FG_RESULTS
		SELECT 
			LT.PROCESSSTEP_ID,L_VENDOR_NAME AS FG_VENDOR,
			LOT_NUMBER,FAB_LOT_NUMBER,LOT_CATEGORY,SHIP_QTY,SHIP_DATE,
			STATUS,
			LOT_DATE_CODE,NULL AS EXPIRY_DATE,DATEDIFF( DD,SHIP_DATE,CURRENT_TIMESTAMP ) AS lot_AGE,
			NULL as EXPECTED_DATE_OUT ,PART_NUMBER,
			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PIN'' AND PROCESSSTEP_ID = PS.PROCESSSTEP_ID ) AS PIN_COUNT,
			 P.PART_STATUS,
			(SELECT PARAMETER_VALUE	FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID ) AS PACKAGE_TYPE,
			NULL AS ASSEMBLY_VENDOR,APT.ASSEMBLY_PROCESSSTEP_ID, APT.TEST_PROCESSSTEP_ID, '''', ''''
		FROM 
			SHIPPED_LOT SL,LOT_TABLE LT, PRODUCT P, PROCESS_STEP PS,#ASSEMBLY_PROCESSSTEP_TABLE APT,LOCATION L
		WHERE 
			SL.LOT_ID = LT.LOT_ID
			AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
			AND PS.PROCESSSTEP_ID = APT.FGPROCESSSTEP_ID
			AND LT.LOCATION_ID = L.LOCATION_ID
			AND SL.STATUS = ''TRANSIT''
			AND PS_TYPE = ''FG'' '
	
		IF @PROCESSSTEP_IDS <> '0'
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
		END
		IF @VENDOR <> '0'
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND L.L_VENDOR_NAME = '''+ @VENDOR +''''
		END
		IF @LOT_CATEGORY <> '0'
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
		END
		/*IF @STATUS = '0'
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' ) '
		END
		ELSE
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
		END*/
		IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
		BEGIN
			SET @FGTRANSIT_STRING = @FGTRANSIT_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
		END
		--PRINT ('@FG_REPORT ----'+@FG_REPORT)
	END
	
	--Inserting Transit Data 
	SET @TRANSIT_STRING = N'
	INSERT INTO #WIP_RESULTS 
		( DATE_IN,QTY_IN,WAFER_QTY_IN,LOT_CURRENT_STATUS,WAFER_ID,PROCESSSTEP_ID, LOCATION, LOT_NUMBER, FAB_LOT_NUMBER,
		LOT_CATEGORY,PART_NUMBER,BASE_PRODUCT,BASE_PRODUCT_ID,PS_TYPE_NAME,PS_TYPE_ID, STAGE_NAME, PACKAGE_TYPE, Date_Code ) 

	SELECT 
		ship_date,
		(CASE WHEN (PS_TYPE != ''FAB'' AND PS_TYPE != ''FAB OPTION'' AND PS_TYPE != ''WAFER BANK'' AND PS_TYPE != ''SORT'' ) THEN SHIP_QTY ELSE NULL END ), 
		(CASE WHEN (PS_TYPE = ''FAB'' OR PS_TYPE = ''FAB OPTION'' OR PS_TYPE = ''WAFER BANK'' OR PS_TYPE = ''SORT'' ) THEN SHIP_QTY ELSE NULL END ),
		status,WAFER_ID,LT.processstep_id,(SELECT L_Vendor_Name FROM Location WHERE Location_ID = Lt.Location_Id ),
		lot_number,fab_lot_number,lt.lot_category,part_number,
		( SELECT Part_Number FROM PRODUCT WHERE Product_Id = ( SELECT P_Parent_Product_Id FROM  Product WHERE ProcessStep_Id = P.ProcessStep_ID )),
		P_Parent_Product_Id,ps_type,
		(CASE
			WHEN PS_TYPE = ''FAB'' THEN 1
			WHEN PS_TYPE = ''WAFER BANK'' THEN 2
			WHEN PS_TYPE = ''Sort'' THEN 3
			WHEN PS_TYPE = ''DIE BANK'' THEN 3
			WHEN PS_TYPE = ''ASSEMBLY'' THEN 5
			WHEN PS_TYPE = ''Pre Test'' THEN 6
			WHEN PS_TYPE = ''FT'' THEN 7
			WHEN PS_TYPE = ''EOL'' THEN 8
			WHEN PS_TYPE = ''Tape & Reel'' THEN 9
			WHEN PS_TYPE = ''FG'' THEN 10
			ELSE NULL 
		END),''Transit'',
		( case 
			when ps_type = ''assembly'' then ( SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER PP  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = lt.PROCESSSTEP_ID )
			when ( ps_type = ''ft'' or ps_type = ''EOL'' or ps_type = ''Pre Test'' or ps_type = ''tape & reel'' or ps_type = ''fg'' ) then 
				( SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER PP,#ASSEMBLY_PROCESSSTEP_TABLE APT  WHERE PARAMETER_KEY = ''PACKAGE ID'' AND PP.PROCESSSTEP_ID = APT.ASSEMBLY_PROCESSSTEP_ID AND FGPROCESSSTEP_ID = lt.PROCESSSTEP_ID )
			else null
		end ), Lot_Date_Code
	FROM 
		SHIPPED_LOT SL,LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS 
	WHERE 
		SL.LOT_ID = LT.LOT_ID
		AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
		AND SL.STATUS <> ''CLOSE'' '
	
	IF @PROCESS_TYPE <> '0'
	BEGIN
		SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND PS.PS_TYPE = '''+ @PROCESS_TYPE+''''
	END
	IF @PROCESSSTEP_IDS <> '0'
	BEGIN
		SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND lt.PROCESSSTEP_ID IN ( SELECT STEP_ID FROM #STEP_IDS_TABLE ) '
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND LT.LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = '''+ @VENDOR +''')'
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND LOT_CATEGORY = '''+ @LOT_CATEGORY+''''
	END
	/*IF @STATUS <> '0'
	BEGIN
		SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND LOT_CURRENT_STATUS = '''+ @STATUS+''''
	END*/
	IF @SEARCH_PART_NO <> '' and LEN(LTRIM(RTRIM(@SEARCH_PART_NO))) > 0
	BEGIN
		SET @TRANSIT_STRING = @TRANSIT_STRING + ' AND  PART_NUMBER like  ''' + '%' + @search_Part_no + '%' + ''''
	END
	
	IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = '0' )
	BEGIN
		EXEC(@FAB_SQLSTRING + @SORT_SQLSTRING + @WS_SQLSTRING + @DB_SQLSTRING + @ASSEMBLY_SQLSTRING + @CLASS_STORE_SQLSTRING + @TEST_FT_SQLSTRING + @EOL_SQLSTRING + @FG_SQLSTRING +@TRANSIT_STRING)
	END
	ELSE IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = 'FAB' )
	BEGIN
		EXEC(@FAB_SQLSTRING+@TRANSIT_STRING)
	END
	ELSE IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = 'WAFER BANK' )
	BEGIN
		EXEC(@WS_SQLSTRING+@TRANSIT_STRING)
	END
	ELSE IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = 'Sort' )
	BEGIN
		EXEC(@SORT_SQLSTRING+@TRANSIT_STRING)
	END
	ELSE IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = 'Pre Test' )
	BEGIN
		EXEC(@CLASS_STORE_SQLSTRING+@TRANSIT_STRING)
	END
	ELSE IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = 'EOL' )
	BEGIN
		EXEC(@EOL_SQLSTRING+@TRANSIT_STRING)
	END
	ELSE IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = 'FT' )
	BEGIN
		EXEC(@TEST_FT_SQLSTRING+@TRANSIT_STRING)
	END
	ELSE IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = 'DIE BANK' )
	BEGIN
		EXEC(@DB_SQLSTRING+@TRANSIT_STRING)
	END
	ELSE IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = 'ASSEMBLY' )
	BEGIN
		EXEC(@ASSEMBLY_SQLSTRING+@TRANSIT_STRING)
	END
	ELSE IF ( @REPORT_TYPE = 'ALL' AND @PROCESS_TYPE = 'TAPE & REEL' )
	BEGIN
		EXEC(@CLASS_STORE_SQLSTRING+@TRANSIT_STRING)
	END
	ELSE IF ( @REPORT_TYPE = 'WIP' AND @PROCESS_TYPE = 'FAB' )
	BEGIN
		EXEC(@FAB_SQLSTRING)
	END
	ELSE IF ( @REPORT_TYPE = 'WIP' AND @PROCESS_TYPE = 'Sort' )
	BEGIN
		EXEC(@SORT_SQLSTRING)
	END
	ELSE IF ( @REPORT_TYPE = 'WIP' AND @PROCESS_TYPE = 'ASSEMBLY' )
	BEGIN
		EXEC(@ASSEMBLY_SQLSTRING)
	END
	ELSE IF ( @REPORT_TYPE = 'INVENTORY' AND @PROCESS_TYPE = 'Pre Test' )
	BEGIN
		EXEC(@CLASS_STORE_SQLSTRING)
	END
	ELSE IF ( @REPORT_TYPE = 'WIP' AND @PROCESS_TYPE = 'FT' )
	BEGIN
		EXEC(@TEST_FT_SQLSTRING)
	END
	ELSE IF ( @REPORT_TYPE = 'INVENTORY' AND @PROCESS_TYPE = 'WAFER BANK' )
	BEGIN
		EXEC(@WS_SQLSTRING)
	END
	ELSE IF ( @REPORT_TYPE = 'INVENTORY' AND @PROCESS_TYPE = 'DIE BANK' )
	BEGIN
		EXEC(@DB_SQLSTRING)
	END
	ELSE IF ( @REPORT_TYPE = 'INVENTORY' AND @PROCESS_TYPE = 'Pre Test' )
	BEGIN
		EXEC(@CLASS_STORE_SQLSTRING)
	END
	ELSE IF ( @REPORT_TYPE = 'INVENTORY' AND @PROCESS_TYPE = 'EOL' )
	BEGIN
		EXEC(@EOL_SQLSTRING)
	END
	/*ELSE IF (@PROCESS_TYPE = 'FG' )
	BEGIN
		EXEC(@FG_REPORT)
	END*/
	ELSE IF ( @REPORT_TYPE = 'WIP' AND @PROCESS_TYPE = 'WIP' )
	BEGIN
		EXEC(@FAB_SQLSTRING + @SORT_SQLSTRING + @ASSEMBLY_SQLSTRING + @TEST_FT_SQLSTRING )
	END
	ELSE IF ( @REPORT_TYPE = 'INVENTORY' AND @PROCESS_TYPE = 'INVENTORY' )
	BEGIN
		EXEC(@WS_SQLSTRING +  @DB_SQLSTRING + @CLASS_STORE_SQLSTRING + @EOL_SQLSTRING + @FG_SQLSTRING)
	END
	ELSE IF ( ( @REPORT_TYPE = 'FINISHED' OR  @REPORT_TYPE = 'INVENTORY' or @REPORT_TYPE = 'all' ) AND @PROCESS_TYPE = 'FG'  )
	BEGIN
		IF @REPORT_TYPE = 'ALL'
		BEGIN
			EXEC(@FG_REPORT+@FGTRANSIT_STRING)
		END
		ELSE
		BEGIN
			EXEC(@FG_REPORT)
		END

		DECLARE @FG_cursor CURSOR
		DECLARE @ASSM_cursor CURSOR

		DECLARE @FG_STEP_ID INT
		DECLARE @ASSM_STEP_ID INT
		DECLARE @TEST_STEP_ID INT
		DECLARE @ASSM_VENDOR VARCHAR(50)
		DECLARE @ASSM_VENDORS VARCHAR(200)
		DECLARE @LOT_NUMBER VARCHAR(50)
		DECLARE @FAB_LOT_NUMBER VARCHAR(50)
		DECLARE @TEST_PROGRAM VARCHAR(100)
		DECLARE @TEST_OUT_DATE DATETIME

		SET @FG_cursor = CURSOR FOR SELECT [PROCESSSTEP ID],[FAB LOT NUMBER],[ASSEMBLY LOT NUMBER],[ASSEMBLY PROCESSSTEP ID],[TEST PROCESSSTEP ID] FROM #FG_RESULTS 
		OPEN @FG_cursor
		FETCH NEXT FROM @FG_cursor INTO @FG_STEP_ID,@FAB_LOT_NUMBER,@LOT_NUMBER,@ASSM_STEP_ID, @TEST_STEP_ID
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			SET @ASSM_cursor = CURSOR FOR SELECT distinct L_VENDOR_NAME 
						FROM 
							LOT_TABLE LT1,LOCATION L1 
						WHERE 
							LT1.LOCATION_ID = L1.LOCATION_ID 
							AND LT1.PROCESSSTEP_ID = @ASSM_STEP_ID
							AND LT1.fab_LOT_NUMBER = @FAB_LOT_NUMBER 
			OPEN @ASSM_cursor 
			FETCH NEXT FROM @ASSM_cursor INTO @ASSM_VENDOR
			WHILE @@FETCH_STATUS = 0
			BEGIN	
				IF ( @ASSM_VENDORS IS NULL )
				BEGIN
					SET @ASSM_VENDORS = @ASSM_VENDOR  
				END
				ELSE
				BEGIN
					SET @ASSM_VENDORS = @ASSM_VENDORS +','+ @ASSM_VENDOR  
				END
			FETCH NEXT FROM @ASSM_cursor INTO @ASSM_VENDOR
			END
			CLOSE @ASSM_cursor 
			DEALLOCATE @ASSM_cursor 

			SELECT TOP 1 @TEST_PROGRAM = FTL_Test_Program, @TEST_OUT_DATE = LT.DATE_OUT FROM Lot_Table LT, Final_Test_Lot FTL WHERE LT.Lot_ID = FTL.Lot_ID AND Lt.ProcessStep_ID = @TEST_STEP_ID AND LT.FAB_LOT_NUMBER = @FAB_LOT_NUMBER
			UPDATE #FG_RESULTS SET [TEST PROGRAM] = @TEST_PROGRAM, [TEST OUT DATE] = @TEST_OUT_DATE WHERE [PROCESSSTEP ID] = @FG_STEP_ID AND [FAB LOT NUMBER] = @FAB_LOT_NUMBER

			UPDATE #FG_RESULTS SET [ASSEMBLY VENDOR] = @ASSM_VENDORS WHERE [PROCESSSTEP ID] = @FG_STEP_ID AND [FAB LOT NUMBER] = @FAB_LOT_NUMBER

				SET @ASSM_VENDORS = NULL
				SET @ASSM_VENDOR  = NULL
				SET @TEST_PROGRAM = NULL
				SET @TEST_OUT_DATE = NULL

			FETCH NEXT FROM @FG_cursor INTO @FG_STEP_ID,@FAB_LOT_NUMBER,@LOT_NUMBER,@ASSM_STEP_ID, @TEST_STEP_ID
		END

		CLOSE @FG_cursor
		DEALLOCATE @FG_cursor

		SELECT 
			[PART NUMBER],[FAB LOT NUMBER],[ASSEMBLY LOT NUMBER],[QTY IN],[FG VENDOR],
		     	[ASSEMBLY VENDOR],[DATE IN],[AGE],[EXPIRY DATE],[PACKAGE TYPE],
			[PIN COUNT],[DATE CODE],[PART STATUS],[LOT STATUS],[LOT CATEGORY],[TEST PROGRAM], [TEST OUT DATE]
		FROM
			#FG_RESULTS ORDER BY [PART NUMBER]
	END

	--Final Result Table retreving resultant data.
	IF @PROCESS_TYPE <> 'FG' and @ASOFDATE = '0'
	BEGIN
		SELECT  
			BASE_PRODUCT, PART_NUMBER, FAB_LOT_NUMBER, LOT_NUMBER, WAFER_QTY_IN, QTY_IN, DATE_CODE, ASSY_REL_NO, LOCATION, PS_TYPE_NAME, STAGE_NAME, STAGE_POSITION,
			DATE_IN, EXPECTED_DATE_OUT, ASSY_REL_DATE, DATEDIFF( DD,DATE_IN,CURRENT_TIMESTAMP )AS AGE, GROSS_DIE,PACKAGE_TYPE,
			(CASE 
				WHEN LOT_CURRENT_STATUS = 'Running' THEN 'ACTIVE'
				ELSE UPPER(LOT_CURRENT_STATUS)
			END) AS LOT_CURRENT_STATUS, 
			LOT_CATEGORY
		FROM 
			#WIP_RESULTS ORDER BY BASE_PRODUCT, PS_TYPE_ID, PART_NUMBER, STAGE_POSITION, PACKAGE_TYPE
	END
	/*ELSE
	BEGIN
		SELECT 
			[MARKETING PART NUMBER],[FAB LOT NUMBER],[ASSEMBLY LOT NUMBER],[FG VENDOR],
		     	[ASSEMBLY VENDOR],[DATE IN],[AGE],[EXPIRY DATE],[QTY IN],[PACKAGE TYPE],
			[PIN COUNT],[DATE CODE],[PART STATUS],[LOT STATUS],[LOT CATEGORY]
		FROM
			#FG_RESULTS ORDER BY [MARKETING PART NUMBER]
	END*/
END
ELSE	
BEGIN
	exec sp_wip_info_data_dump_as_of_date @PROCESS_TYPE,@PROCESSSTEP_IDS,@VENDOR,@LOT_CATEGORY,@ASOFDATE,@STATUS,@REPORT_TYPE,@SEARCH_PART_NO
END

END

